﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using WMSSyncData.Model.WMSModel;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.Oracle
{
    public class WMS_M_OutStockRMDAL : BaseOracleDAL<WMS_T_OutStockRM>
    {
        /// <summary>
        /// 获取MES叫料单头数据
        /// </summary>
        /// <param name="billtype"></param>
        /// <returns></returns>
        public List<WMS_T_OutStockRM> GetMesHead(string billtype)
        {
            var sql = string.Format(@"select DISTINCT BillNo,BillType,WorkOrder as RelatedBill，NVL(OutWarehouse, ' ') as Storage_SN,
                        0 as Status,Creator,CreateTime,Ext1,Ext2,LineName,Lane from wms_M_CallOrder108 where Data_Status='1' and BillType='{0}'", billtype);
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        /// <summary>
        /// 更新MES叫料单明细数据读取状态
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int CloseMesHead(List<WMS_T_OutStockRM> list)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (var item in list)
            {
                var sql = "UPDATE wms_M_CallOrder108 set  Data_Status=2,Data_ReadTime=SYSDATE where BillNo='" + item.BillNo + " and Data_Status=1;";
                sql += "UPDATE wms_M_CallOrderDetail108 set  Data_Status=2,Data_ReadTime=SYSDATE where BillNo='" + item.BillNo + "and Data_Status=1;";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        /// <summary>
        /// 获取生产领料信息
        /// </summary>
        /// <param name="sns"></param>
        /// <returns></returns>
        public List<WMS_T_OutStockRM> GetSyncData(string billtype)
        {
            var sql = string.Format(@"select DISTINCT BillNo,BillType,RelatedBillNo as RelatedBill，NVL(OutWarehouse, ' ') as Storage_SN,
                        0 as Status,Creator ,CreateTime, Ext1, Ext2 from wms_M_outStockRM where Data_Status='1' and BillType='{0}'", billtype);
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }


        public List<WMS_T_OutStockRM> GetSyncOutStockData()
        {
            var sql = @"select 'B0201'||to_char(sysdate, 'yyMMdd')||LPAD (SEQ_WMS_M_Instock.NEXTVAL,5,'0') AS BillNo,temp.* from (
                        select DISTINCT t$orno AS RelatedBill, '采购退货'  as   BillType,'RMWHS' AS Storage_SN,t$cwar AS Ext3,0 AS Status,t$otbp as Supplier,
                        'admin' as Creator,SYSDATE AS CreateTime,T$REVI as Ext2 from  TTDDEV101101 where t$ITTP='发料' and t$host='1' and t$fire<>1 and substr(t$orno,0,3)!='HFB' and substr(t$orno,0,3)!='HPR' and substr(t$orno,0,3)!='HUS' and substr(t$orno,0,3)!='HFR' ) temp";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }




        public List<WMS_T_OutStockRM> GetSyncRMByERPBOMData(string sns)
        {

            var sql = string.Format(@"select  'B0201'||to_char(sysdate, 'yyMMdd')||LPAD (SEQ_WMS_M_Instock.NEXTVAL,5,'0') AS BillNo, temp.* from 
(select DISTINCT T$PDNO as RelatedBill,'非拉动领料出库' as BillType,
                        0 as Status,'admin' as Creator ,SYSDATE as CreateTime,T$CWAR as Ext1,T$REVI as Ext2 from TTDDEV104101 where 
												T$QANA>0 and t$WMSHOST='1'  and t$bfls='2' and T$CWAR   in (" + sns + ")) temp");
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        public List<WMS_T_OutStockRM> GetSyncUpdateRMByERPBOMData(string sns)
        {

            var sql = string.Format(@"select  'B0201'||to_char(sysdate, 'yyMMdd')||LPAD (SEQ_WMS_M_Instock.NEXTVAL,5,'0') AS BillNo, temp.* from 
(select DISTINCT T$PDNO as RelatedBill,'非拉动领料出库' as BillType,
                        0 as Status,'admin' as Creator ,SYSDATE as CreateTime,T$CWAR as Ext1,T$REVI as Ext2 from TTDDEV104101 where 
												T$QANA>0 and t$WMSHOST='3'  and t$bfls='2'  and T$CWAR   in (" + sns + ")) temp");
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        public DataTable GetInverUpdateDate()
        {
            var sql = string.Format(@"select t$orno,t$pono,t$item,t$cwar,t$qstk,t$cuni,t$koor,t$host,t$edte1,t$wdte1,t$refcntd,T$REFCNTU,t$itid,t$itse from  ttddev118101 where T$HOST=1 AND T$qstk>0");
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            return dt;
        }


        public int UpdateStatus(List<DataRow> drs)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (DataRow dr in drs)
            {
                var sql = "UPDATE ttddev118101 set  T$HOST=2,t$wdte1=SYSDATE where t$orno='" + dr["t$orno"].ToString() + "' and t$pono='" + dr["t$pono"].ToString() + "' and t$itid='" + dr["t$itid"].ToString() + "' and t$itse='" + dr["t$itse"].ToString() + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

    }

    public class WMS_M_OutStockRMRowsDAL : BaseOracleDAL<WMS_T_OutStockRMRows>
    {

        public int UpdateStatus(List<WMS_T_OutStockRMRows> list)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (var item in list)
            {
                var sql = "UPDATE TTDDEV101101 set  t$host=2,t$wdte1=SYSDATE where t$orno='" + item.RelatedBill + "' and t$pono='" + item.RowNo + "' and t$sqnb='" + item.OrderNo + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }


        public DataTable getDeleteRows(List<WMS_T_OutStockRMRows> list)
        {
            string sqlWhere = string.Empty;
            if (list.Count() > 0)
            {
                sqlWhere = " and (";
                foreach (var item in list)
                {
                    sqlWhere = sqlWhere + string.Format(" (RelatedBill='{0}' and RowNo='{1}' and OrderNo='{2}') or", item.RelatedBill, item.RowNo, item.OrderNo);
                }
                sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 2) + ") ";
            }
            else
            {
                sqlWhere = " and 1<>1";
            }
            var sql = "select *,(select count(1) from WMS_T_OutStockRMRows a where a.billno=wms_T_InStockRMRows.billno) as billcount  from WMS_T_OutStockRMRows where 1=1 " + sqlWhere + " and billno not in(select distinct billno from wms_T_OutStockRMRowDetails )";
            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            return dt;
        }

        public bool DeleteRowAndInfo(DataTable dtRow, List<string> InfoList)
        {
            var result = false;

            string sql = string.Empty;
            foreach (DataRow dr in dtRow.Rows)
            {
                sql = sql + string.Format(@"delete wms_T_OutStockRMRows   WHERE BillNo='{0}' and RowNo='{1}' and OrderNo='{2}';", dr["BillNo"].ToString(), dr["RowNo"].ToString(), dr["OrderNo"].ToString());
            }
            foreach (string billno in InfoList)
            {
                sql = sql + string.Format(@"delete wms_T_OutStockRM   WHERE BillNo='{0}' ;", billno);
            }
            var res = SqlDBHelper.ExcuteSQL(sql);
            if (res > 0)
            {
                result = true;
            }
            return result;
        }

        public int UpdateDeleteRowStatus(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (DataRow item in dt.Rows)
            {
                var sql = "UPDATE TTDDEV101101 set  t$host=2,t$wdte1=SYSDATE where t$orno='" + item["RelatedBill"].ToString() + "' and t$pono='" + item["RowNo"].ToString() + "' and t$sqnb='" + item["OrderNo"].ToString() + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }


        public List<WMS_T_OutStockRMRows> GetSyncData()
        {
            var sql = @"select  t$orno AS RelatedBill,t$item AS PartNumber,t$pono AS RowNo,t$sqnb AS OrderNo, -t$qoor AS PlanQty,0 AS RealQty,t$cuqp AS Unit, 0 AS Status,'admin' as Creator,SYSDATE AS CreateTime,T$REVI AS Ext3  from  TTDDEV101101 where t$ITTP='发料' and t$host=1 and t$fire<>'1' and substr(t$orno,0,3)!='HFB' and substr(t$orno,0,3)!='HPR' and substr(t$orno,0,3)!='HUS' and substr(t$orno,0,3)!='HFR' ";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            var result = MakeTablePackage(dt);
            return result;
        }

        public List<WMS_T_OutStockRMRows> GetSyncDeleteData()
        {
            var sql = @"select  t$orno AS RelatedBill,t$item AS PartNumber,t$pono AS RowNo,t$sqnb AS OrderNo,t$qoor AS PlanQty,0 AS RealQty,t$cuqp AS Unit, 0 AS Status,'admin' as Creator,SYSDATE AS CreateTime,T$REVI AS Ext3  from  TTDDEV101101 where t$ITTP='发料' and t$host=1 and t$fire='1' and substr(t$orno,0,3)!='HFB' and substr(t$orno,0,3)!='HPR' and substr(t$orno,0,3)!='HUS' and substr(t$orno,0,3)!='HFR' ";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            var result = MakeTablePackage(dt);
            return result;
        }

        public int UpdateStatus(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (DataRow dr in dt.Rows)
            {
                var sql = "UPDATE TTDDEV101101 set  t$host=2,t$wdte1=SYSDATE where t$orno='" + dr["RelatedBill"].ToString() + "' and t$pono='" + dr["RowNo"].ToString() + "' and t$sqnb='" + dr["SN"].ToString() + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }


        /// <summary>
        /// 获取生产退料数据
        /// </summary>
        /// <returns></returns>
        public List<WMS_T_OutStockRMRows> GetSyncData(string billtype)
        {

            var sql = string.Format(@"SELECT   BillNO,RelatedbillNo as RelatedBill,PartNumber,RowNo,SequenceNo as OrderNo,OutQty as PlanQty,Unit,
                        0 AS status,creator as Creator,createtime as createtime,Ext1,Ext2,Ext3 FROM WMS_M_outStockRM 
                        WHERE Data_Status=1 and billtype='{0}'", billtype);
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        /// <summary>
        /// 获取MES叫料单明细数据
        /// </summary>
        /// <returns></returns>
        public List<WMS_T_OutStockRMRows> GetMesDetail(string billtype)
        {
            var sql = string.Format(@"SELECT d.BillNo,m.WorkOrder as RelatedBill,d.PartNumber,d.RowNo,0 as OrderNo,d.NeedQty as PlanQty,
d.Unit,0 AS Status,'mes' as Creator,d.Data_WriteTime as Createtime,d.Ext1,d.Ext2,d.Ext3 
FROM wms_M_CallOrderDetail108 d inner join wms_M_CallOrder108 m on d.BillNo=m.BillNo
WHERE d.Data_Status=1 and m.Data_Status=1 and m.BillType='{0}'", billtype);
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        public int UpdatePOMStatus(List<WMS_T_OutStockRMRows> list)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (var item in list)
            {
                var sql = "UPDATE WMS_M_OUTSTOCKRM set  Data_Status=2,Data_ReadTime=SYSDATE where BillNo='" + item.BillNo + "' and RowNo='" + item.RowNo + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public int UpdateBOMStatus(List<WMS_T_OutStockRMRows> list)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (var item in list)
            {
                var sql = "UPDATE TTDDEV104101 set  T$WMSHOST='2'  where    t$bfls='2' and T$PDNO='" + item.RelatedBill + "' and T$PONO ='" + item.RowNo + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }



        public List<WMS_T_OutStockRMRows> GetSyncRMByERPBOMData(string sns)
        {

            var sql = string.Format(@"SELECT   T$PDNO as RelatedBill,T$SITM as PartNumber,T$PONO as RowNo,T$QANA as PlanQty,T$CUQP as Unit,
                        0 AS status,'admin' as Creator,SYSDATE as createtime,T$CWAR as Ext1,T$REVI as Ext3 FROM TTDDEV104101 
                         where 	T$QANA>0 and T$WMSHOST='1'  and t$bfls='2' and T$CWAR   in (" + sns + ")");
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        public List<WMS_T_OutStockRMRows> GetSyncRMUpdateByERPBOMData(string sns)
        {

            var sql = string.Format(@"SELECT   T$PDNO as RelatedBill,T$SITM as PartNumber,T$PONO as RowNo,T$QANA as PlanQty,T$CUQP as Unit,
                        0 AS status,'admin' as Creator,SYSDATE as createtime,T$CWAR as Ext1,T$REVI as Ext3 FROM TTDDEV104101 
                         where 	T$QANA>0 and T$WMSHOST='3'  and t$bfls='2' and T$CWAR   in (" + sns + ")");
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }


        public List<WMS_T_OutStockRMRows> GetSyncRMDeleteByERPBOMData(string sns)
        {

            var sql = string.Format(@"SELECT   T$PDNO as RelatedBill,T$SITM as PartNumber,T$PONO as RowNo,T$QANA as PlanQty,T$CUQP as Unit,
                        0 AS status,'admin' as Creator,SYSDATE as createtime,T$CWAR as Ext1,T$REVI as Ext3 FROM TTDDEV104101 
                         where 	T$QANA>0 and T$WMSHOST='4'  and t$bfls='2' and T$CWAR   in (" + sns + ")");
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

    }

}
